package persistence.impl;

import domain.Item;
import domain.Product;
import persistence.DBUtil;
import persistence.ItemDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ItemDAOImpl implements ItemDao {
    private final String UPDATE_INVENTORY_QUANTITY=
            "UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID =? ";
    //#{increment}是什么意思？
    private final String GET_INVENTORY_QUANTITY=
            "SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";
    private final String GET_ITEM_LIST_BYPRODUCT=
            "SELECT I.ITEMID, LISTPRICE, UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID ,NAME ,DESCN ,CATEGORY , STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5 FROM ITEM I, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID and I.PRODUCTID = ?";

    private final String GET_ITEM=
            "SELECT I.ITEMID,LISTPRICE,UNITCOST,SUPPLIER AS supplierId,I.PRODUCTID ,NAME ,DESCN ,CATEGORY  ,STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5,QTY AS quantity FROM ITEM I, INVENTORY V, PRODUCT P where P.PRODUCTID = I.PRODUCTID and I.ITEMID = V.ITEMID and I.ITEMID = ?";




    @Override
    public void updateInventoryQuantity(Map<String, Object> var1) {
        try{


            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(UPDATE_INVENTORY_QUANTITY);
//            String itemId=var1.keySet().iterator().next();
//            Integer increment=(Integer)var1.get(itemId);
            String itemId = var1.get("itemId").toString();
            Integer increment = (Integer) var1.get("increment");
            preparedStatement.setInt(1,increment.intValue());
            preparedStatement.setString(2,itemId);
            preparedStatement.execute();

            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }

    }



    @Override
    public int getInventoryQuantity(String var1) {
        int inventory = -1;//此处具体值有待讨论
        try{
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_INVENTORY_QUANTITY);
            preparedStatement.setString(1,var1);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next())
            {
                inventory=resultSet.getInt("value");


            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return inventory;
    }

    @Override
    public List<Item> getItemListByProduct(String var1) {
        List<Item> itemList=new ArrayList<>();
        try{
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_ITEM_LIST_BYPRODUCT);
            preparedStatement.setString(1,var1);
            ResultSet resultSet=preparedStatement.executeQuery();
            while(resultSet.next())
            {
                Item item=new Item();
                Product product=new Product();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));

                item.setProductId(resultSet.getString(5));
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));

                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));

                item.setProduct(product);
                itemList.add(item);

            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return itemList;
    }

    @Override
    public Item getItem(String var1) {
        Item item=null;
        try{
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(GET_ITEM);
            preparedStatement.setString(1,var1);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next())
            {
                item=new Item();
                Product product=new Product();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));

                item.setProductId(resultSet.getString(5));
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));

                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                item.setQuantity(resultSet.getInt(15));

                item.setProduct(product);



            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return item;
    }
}
